package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import model.Board;

/*int num, String name, String pass, String subject,
String content, String file, int re_ref, int re_lev, int re_seq,
int readcount, Date regdate */

public class BoardDAO {
	Connection con;
	PreparedStatement pstmt;
	ResultSet rs;

	public BoardDAO(){
		try{
			Context init = new InitialContext();
			DataSource ds=(DataSource) init.lookup(
					"java:comp/env/jdbc/mysql");
			con=ds.getConnection();
		}catch(Exception e){
			System.err.println("DB연결 실패 ");
			e.printStackTrace();
		}
	}
	
	public static void insertTest(){
		Board testBoard=new Board(
				0,"kim","1","1subject","1content","1file",0,0,0,0,null);
		BoardDAO boardDAO=new BoardDAO();
		boolean outcome=boardDAO.insert(testBoard);
		System.out.println("insert 결과 :"+ outcome);
	}
	
	public static void getListCountTest(){
		BoardDAO boardDAO=new BoardDAO();
		int outcome=boardDAO.getListCount();
		System.out.println("getListCountTest 결과 :"+ outcome);
	}
	public static void getBoardList(){
		BoardDAO boardDAO=new BoardDAO();
		List<Board> list=boardDAO.getBoardList(1,20);
		for(Board board : list){
			System.out.println(board); 
		}
	}
	public static void deleteTest(int num){
		BoardDAO boardDAO=new BoardDAO();
		if(boardDAO.delete(num)){
			System.out.println(num+"번 글 지우기 테스트 성공");
		}else{
			System.out.println(num+"번 글 지우기 테스트 실패"); 
		}
	}
	
	static public void testReply(){
		Board testBoard=new Board(
				5,"R park","r","R subject","Rcontent","Rfile",0,0,0,0,null);
		int num=new BoardDAO().reply(testBoard);
		System.out.println(num+"번 답글을 달았습니다");  
	}
	
	//조회수 업데이트 p389 아래
	public void setReadCountUpdate(int num){
		String sql="UPDATE board SET readcount=readcount+1 "
					+" where num="+num;
		try{
			con.createStatement().executeUpdate(sql);
		}catch(Exception e){
			System.err.println("BoardDAO.setReadCountUpdate() 에러발생");
			e.printStackTrace(); 
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException e){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException e){}
		}	
	}
	//글쓴이인지 확인 p390
	public boolean isBoardWriter(int num, String pass){
		String sql="SELECT * FROM board where num="+num;
		try{
			rs=con.createStatement().executeQuery(sql);
			if(rs.next()){
				if(pass.equals(rs.getString("pass")))
					return true;
				else
					System.out.println(pass+" 비번이 틀립니다");				
			}else{
				System.out.println("isBoardWriter 체크 요청"); 
				System.out.println("num="+num+", pass="+pass);
			}
		}catch(Exception e){
			System.err.println("BoardDAO.getList() 에러발생");
			e.printStackTrace(); 
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException e){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException e){}
		}	return false;
	}
	
	//글 답변 p387 아래
	public int reply(Board board){
		StringBuilder sql=new StringBuilder();	
		try{
			sql.append("UPDATE board SET re_seq=re_seq+1 ");
			sql.append(" where re_ref=? and re_seq>?");  
			pstmt=con.prepareStatement(sql.toString());
			pstmt.setInt(1, board.getRe_ref());
			pstmt.setInt(2, board.getRe_seq());  
			if(pstmt.executeUpdate()==0){
				System.out.println("update 된 행이 없습니다");
			}
			// insert 메서드를 호출해서 답변을 insert 시킵니다
			board.setRe_seq(board.getRe_seq()+1); 
			board.setRe_lev(board.getRe_lev()+1); 
			if(insert(board)){
				System.out.println("답변 달기 성공");
				return getRecentNumber();
			}			
		}catch(Exception e){
			System.err.println("BoardDAO.reply() 에러발생");
			e.printStackTrace(); 
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException e){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException e){}
		}return -1;	
	}
	
	//글 수정 p388 아래
	public boolean modify(Board modifyBoard ){
		String sql="UPDATE board SET subject=?, content=? "
				+" where num=?";
		try{
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, modifyBoard.getSubject());
			pstmt.setString(2, modifyBoard.getContent()); 
			pstmt.setInt(3, modifyBoard.getNum());
			int outcome=-1;
			outcome=pstmt.executeUpdate();
			if(outcome==-1){
				System.out.println("update 실패"); 
			}System.out.println( 
				outcome+"개의 행이 업데이트 되었습니다 update 성공");
			return true;
		}catch(Exception e){
			System.err.println("BoardDAO.getList() 에러발생");
			e.printStackTrace(); 
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException e){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException e){}
		}		
		return false;
	}
	
	//글 내용보기 p386
	public Board getDetail(int num){
		Board board=null;
		try{
			pstmt=con.prepareStatement(
				"SELECT * FROM board where num=?");
			pstmt.setInt(1, num); 
			rs=pstmt.executeQuery();
			while(rs.next()){
				board= new Board();
				board.setNum(rs.getInt("num"));  
				board.setName(rs.getString("name"));
				//board.setPass(rs.getString("pass"));
				board.setSubject(rs.getString("subject"));
				board.setContent(rs.getString("content"));
				board.setFile(rs.getString("file"));
				board.setRe_ref(rs.getInt("re_ref"));
				board.setRe_lev(rs.getInt("re_lev"));
				board.setRe_seq(rs.getInt("re_seq"));
				board.setReadcount(rs.getInt("readcount"));
				board.setRegdate(rs.getDate("regdate")); 				
			}
			return board;
		}catch(Exception e){
			System.err.println("BoardDAO.getDetail() 에러발생");
			e.printStackTrace(); 
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException e){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException e){}
		}		
		return null; 
	}
	
	//글 삭제
	public boolean delete(int num){
		try{
			String sql="DELETE from board where num=?";
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1, num); 
			System.out.println(num+"번 글을 지우려고 합니다 ");
			int outcome=-1;
			outcome=pstmt.executeUpdate();
			if(outcome==-1){
				System.out.println("delete 체크 요망");
				return false;
			}else if(outcome==0){
				System.out.println(num+"번 글이 있는지 확인 요망");
				return false;
			}else{
				System.out.println(num+"번 글 delete 성공");  
			}			
		}catch(Exception e){
			System.err.println("BoardDAO.delete() 에러발생");
			e.printStackTrace(); 
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException e){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException e){}
		}	
		return true;
	}
	
	//글 목록 보기
	public List<Board> getBoardList(int page, int limit){
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT * FROM board order by re_ref desc, ");
		sql.append("re_seq asc limit ?,?"); 
		List<Board> list=new ArrayList<Board>();
		
		int startrow=(page-1)*10; //읽기 시작할 번호
		int endrow=startrow+limit-1; //읽을 마지막 번호
		try{
			pstmt=con.prepareStatement(sql.toString());
			pstmt.setInt(1, startrow);
			pstmt.setInt(2, endrow);
			rs=pstmt.executeQuery();  
			System.out.println("select 성공"); 
			while(rs.next()){
				Board board= new Board();
				board.setNum(rs.getInt("num"));  
				board.setName(rs.getString("name"));
				board.setPass(rs.getString("pass"));
				board.setSubject(rs.getString("subject"));
				board.setContent(rs.getString("content"));
				board.setFile(rs.getString("file"));
				board.setRe_ref(rs.getInt("re_ref"));
				board.setRe_lev(rs.getInt("re_lev"));
				board.setRe_seq(rs.getInt("re_seq"));
				board.setReadcount(rs.getInt("readcount"));
				board.setRegdate(rs.getDate("regdate")); 
				list.add(board);
			}
		}catch(Exception e){
			System.err.println("BoardDAO.getBoardList() 에러발생");
			e.printStackTrace(); 
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException e){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException e){}
		}		
		return list;
	}
	
	//글의 개수 구하기 p384
	public int getListCount(){
		int x=-1; 
		try{
			pstmt=con.prepareStatement("select count(*) from board");
			rs=pstmt.executeQuery();
			if(rs.next())
				x=rs.getInt(1);
		}catch(Exception e){
			System.err.println("BoardDAO.getListCount() 에러발생");
			e.printStackTrace(); 
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException e){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException e){}
		}		
		return x;
	}
	
	// 마지막에 저장한 글번호 가져오기
	public int getRecentNumber(){
		try{
			String sql="select ifnull(max(num),0) from board";
			rs=con.createStatement().executeQuery(sql);
			if(rs.next()){
				return rs.getInt(1);
			}
			System.out.println("최근글번호가져오기 체크 요망"); 
		}catch(Exception e){
			System.err.println("BoardDAO.getList() 에러발생");
			e.printStackTrace(); 
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException e){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException e){}
		}
		return -1;  
	}
	
	//글 등록
	public boolean insert(Board board){
		StringBuilder sql=new StringBuilder();
		sql.append("INSERT INTO board (name,pass,subject");
		sql.append(",content,file,re_ref, re_lev,re_seq,readcount");
		sql.append(",regdate) values (?,?,?,?,?,0,0,0,0,sysdate())");
		int result=-1;
		try{
			pstmt=con.prepareStatement(sql.toString());
			pstmt.setString(1, board.getName());
			pstmt.setString(2, board.getPass());
			pstmt.setString(3, board.getSubject());
			pstmt.setString(4, board.getContent());
			pstmt.setString(5, board.getFile());			
			result=pstmt.executeUpdate();
			if(result==-1){
				System.err.println("BoardDAO.insert() insert되지 않았다");
				return false;
			}
			System.out.println("BoardDAO.insert() 성공 "+board); 
			// ref_num을 num값과 일치시키자 
			
			// 방금 넣은 num 번호를 알아오자
			int recentNum=getRecentNumber();
			
			String update="UPDATE board set re_ref=? where num=?";
			pstmt.close();
			pstmt=con.prepareStatement(update);  
			if(board.getNum()<=0){
				pstmt.setInt(1, recentNum);  //일반 글일 경우 
			}else
				pstmt.setInt(1, board.getNum());   //답글일 경우
			pstmt.setInt(2, recentNum);
			result=-2;
			result=pstmt.executeUpdate();
			if(result==-2){
				System.out.println("re_ref 의 업데이트 실패");
				return false;
			}
			System.out.println("re_ref 의 업데이트 성공");  
			return true;
		}catch(Exception e){
			System.err.println("BoardDAO.insert() 에러발생");
			e.printStackTrace(); 
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException e){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException e){}
		}		
		return false;
	}
	public List getList(){
		try{
			
		}catch(Exception e){
			System.err.println("BoardDAO.getList() 에러발생");
			e.printStackTrace(); 
		}finally{
			if(rs!=null) try{rs.close();}catch(SQLException e){}
			if(pstmt!=null) try{pstmt.close();}catch(SQLException e){}
		}		
		return null;
	}

}
